using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public static partial class VideoLocationService
    {
        public static bool AddVideoLocation(VideoLocation videoLocation)
        {
            string sql =
                "INSERT VideoLocation (Location, IconID, VideoID)" +
                "VALUES (@Location, @IconID, @VideoID)";

            sql += " ; SELECT @@IDENTITY";


            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@Location", videoLocation.Location),
				new SqlParameter("@IconID", videoLocation.IconID),
				new SqlParameter("@VideoID", videoLocation.VideoID)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }


        public static bool DeleteVideoLocationByID(int iD)
        {
            string sql = "DELETE VideoLocation WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", iD)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        public static bool ModifyVideoLocation(VideoLocation videoLocation)
        {
            string sql =
                "UPDATE VideoLocation " +
                "SET " +
                    "Location = @Location, " +
                    "IconID = @IconID, " +
                    "VideoID = @VideoID " +
                "WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", videoLocation.ID),
				new SqlParameter("@Location", videoLocation.Location),
				new SqlParameter("@IconID", videoLocation.IconID),
				new SqlParameter("@VideoID", videoLocation.VideoID)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static IList<VideoLocation> GetAllVideoLocations()
        {
            string sqlAll = "SELECT * FROM VideoLocation";
            return GetVideoLocationsBySql(sqlAll);
        }

        public static IList<VideoLocation> GetVideoLocationsByVideoID(int videoID)
        {
            string sqlAll = "SELECT * FROM VideoLocation WHERE VideoID = @VideoID and Location <> ''";
            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@VideoID", videoID)
			};
            return GetVideoLocationsBySql(sqlAll, para);
        }

        public static VideoLocation GetVideoLocationByID(int iD)
        {
            string sql = "SELECT * FROM VideoLocation WHERE VideoID = @VideoID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", iD)
			};

            DataTable temp = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);

            VideoLocation videoLocation = new VideoLocation();

            foreach (DataRow dr in temp.Rows)
            {
                videoLocation.ID = (int)dr["ID"];
                videoLocation.Location = (string)dr["Location"];
                videoLocation.IconID = (int)dr["IconID"];
                videoLocation.VideoID = (int)dr["VideoID"];

            }
            return videoLocation;

        }

        public static VideoLocation GetVideoLocationByIDAndIconID(int videoID, int iconID)
        {
            string sql = "SELECT * FROM VideoLocation WHERE VideoID = @VideoID and IconID = @IconID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@VideoID", videoID),
                new SqlParameter("@IconID", iconID)
			};

            DataTable temp = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);

            VideoLocation videoLocation = new VideoLocation();

            foreach (DataRow dr in temp.Rows)
            {
                videoLocation.ID = (int)dr["ID"];
                videoLocation.Location = (string)dr["Location"];
                videoLocation.IconID = (int)dr["IconID"];
                videoLocation.VideoID = (int)dr["VideoID"];

            }
            return videoLocation;

        }

        public static VideoLocation GetVideoLocationBytop1(int videoID)
        {
            string sql = "SELECT top 1 * FROM VideoLocation WHERE VideoID=@VideoID and Location <> ''";

            SqlParameter[] para = new SqlParameter[]
			{
                new SqlParameter("@VideoID", videoID)
			};

            DataTable temp = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);

            VideoLocation videoLocation = new VideoLocation();

            foreach (DataRow dr in temp.Rows)
            {
                videoLocation.ID = (int)dr["ID"];
                videoLocation.Location = (string)dr["Location"];
                videoLocation.IconID = (int)dr["IconID"];
                videoLocation.VideoID = (int)dr["VideoID"];

            }
            return videoLocation;

        }

        public static VideoLocation GetVideoLocationByIDAndV(int iD, int videoID)
        {
            string sql = "SELECT * FROM VideoLocation WHERE IconID = @IconID and VideoID=@VideoID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@IconID", iD),
                new SqlParameter("@VideoID", videoID)
			};

            DataTable temp = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);

            VideoLocation videoLocation = new VideoLocation();

            foreach (DataRow dr in temp.Rows)
            {
                videoLocation.ID = (int)dr["ID"];
                videoLocation.Location = (string)dr["Location"];
                videoLocation.IconID = (int)dr["IconID"];
                videoLocation.VideoID = (int)dr["VideoID"];

            }
            return videoLocation;

        }


        private static IList<VideoLocation> GetVideoLocationsBySql(string safeSql)
        {
            List<VideoLocation> list = new List<VideoLocation>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, null))
            {
                while (dr.Read())
                {
                    VideoLocation videoLocation = new VideoLocation();

                    videoLocation.ID = (int)dr["ID"];
                    videoLocation.Location = (string)dr["Location"];
                    videoLocation.IconID = (int)dr["IconID"];
                    videoLocation.VideoID = (int)dr["VideoID"];

                    list.Add(videoLocation);
                }
            }
            return list;
        }

        private static IList<VideoLocation> GetVideoLocationsBySql(string safeSql, params SqlParameter[] para)
        {
            List<VideoLocation> list = new List<VideoLocation>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, para))
            {
                while (dr.Read())
                {
                    VideoLocation videoLocation = new VideoLocation();

                    videoLocation.ID = (int)dr["ID"];
                    videoLocation.Location = (string)dr["Location"];
                    videoLocation.IconID = (int)dr["IconID"];
                    videoLocation.VideoID = (int)dr["VideoID"];

                    list.Add(videoLocation);
                }
            }
            return list;
        }
    }
}
